USE [master]
GO

/****** Object:  Database [MovieRentalDB]    Script Date: 04/12/2012 21:34:03 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MovieRentalDB')
DROP DATABASE [MovieRentalDB]
GO

USE [master]
GO

/****** Object:  Database [MovieRentalDB]    Script Date: 04/12/2012 21:34:03 ******/
CREATE DATABASE [MovieRentalDB] ON  PRIMARY 
( NAME = N'MovieRentalDb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MovieRentalDb.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MovieRentalDb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MovieRentalDB_1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [MovieRentalDB] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MovieRentalDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [MovieRentalDB] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [MovieRentalDB] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [MovieRentalDB] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [MovieRentalDB] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [MovieRentalDB] SET ARITHABORT OFF 
GO

ALTER DATABASE [MovieRentalDB] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [MovieRentalDB] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [MovieRentalDB] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [MovieRentalDB] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [MovieRentalDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [MovieRentalDB] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [MovieRentalDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [MovieRentalDB] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [MovieRentalDB] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [MovieRentalDB] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [MovieRentalDB] SET  DISABLE_BROKER 
GO

ALTER DATABASE [MovieRentalDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [MovieRentalDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [MovieRentalDB] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [MovieRentalDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [MovieRentalDB] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [MovieRentalDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [MovieRentalDB] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [MovieRentalDB] SET  READ_WRITE 
GO

ALTER DATABASE [MovieRentalDB] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [MovieRentalDB] SET  MULTI_USER 
GO

ALTER DATABASE [MovieRentalDB] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [MovieRentalDB] SET DB_CHAINING OFF 
GO

USE [MovieRentalDB]
GO



USE [MovieRentalDb]
GO

USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[Customers]    Script Date: 04/15/2012 01:00:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Customers](
	[Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Email] [nvarchar](50) NOT NULL,
	[PhoneNo] [nvarchar](30) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Customers] ADD  CONSTRAINT [DF_Customers_Id]  DEFAULT (newid()) FOR [Id]
GO


USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[Movies]    Script Date: 04/15/2012 01:01:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Movies](
	[Id] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	[ImdbRating] [float] NOT NULL,
	[WebPage] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[MediaTypes]    Script Date: 04/15/2012 01:01:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MediaTypes](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](30) NOT NULL,
	[DefaultPricePerDay] [float] NOT NULL,
 CONSTRAINT [PK_MediaTypes] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[Discounts]    Script Date: 04/15/2012 01:02:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Discounts](
	[Id] [uniqueidentifier] NOT NULL,
	[Value] [float] NOT NULL,
 CONSTRAINT [PK_Discounts] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[PersonalDiscounts]    Script Date: 04/15/2012 01:02:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PersonalDiscounts](
	[Id] [uniqueidentifier] NOT NULL,
	[Money] [float] NOT NULL,
 CONSTRAINT [PK_PersonalDiscounts] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PersonalDiscounts]  WITH CHECK ADD  CONSTRAINT [FK_PersonalDiscounts_Discounts] FOREIGN KEY([Id])
REFERENCES [dbo].[Discounts] ([Id])
GO

ALTER TABLE [dbo].[PersonalDiscounts] CHECK CONSTRAINT [FK_PersonalDiscounts_Discounts]
GO



USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[CartDiscounts]    Script Date: 04/15/2012 01:02:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CartDiscounts](
	[Id] [uniqueidentifier] NOT NULL,
	[Quantity] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CartDiscounts]  WITH CHECK ADD  CONSTRAINT [FK_CartDiscounts_Discounts] FOREIGN KEY([Id])
REFERENCES [dbo].[Discounts] ([Id])
GO

ALTER TABLE [dbo].[CartDiscounts] CHECK CONSTRAINT [FK_CartDiscounts_Discounts]
GO




USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[TemporaryDiscounts]    Script Date: 04/15/2012 01:03:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TemporaryDiscounts](
	[Id] [uniqueidentifier] NOT NULL,
	[ValidUntil] [date] NOT NULL,
 CONSTRAINT [PK_TemporaryDiscounts] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TemporaryDiscounts]  WITH CHECK ADD  CONSTRAINT [FK_TemporaryDiscounts_Discounts] FOREIGN KEY([Id])
REFERENCES [dbo].[Discounts] ([Id])
GO

ALTER TABLE [dbo].[TemporaryDiscounts] CHECK CONSTRAINT [FK_TemporaryDiscounts_Discounts]
GO


USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[Editions]    Script Date: 04/15/2012 01:03:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Editions](
	[Id] [uniqueidentifier] NOT NULL,
	[Publisher] [nvarchar](50) NOT NULL,
	[PricePerDay] [float] NOT NULL,
	[MediaTypeId] [uniqueidentifier] NOT NULL,
	[MovieId] [uniqueidentifier] NOT NULL,
	[DiscountId] [uniqueidentifier] NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_Editions] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Editions]  WITH CHECK ADD  CONSTRAINT [FK_Editions_MediaTypes] FOREIGN KEY([MediaTypeId])
REFERENCES [dbo].[MediaTypes] ([Id])
GO

ALTER TABLE [dbo].[Editions] CHECK CONSTRAINT [FK_Editions_MediaTypes]
GO

ALTER TABLE [dbo].[Editions]  WITH CHECK ADD  CONSTRAINT [FK_Editions_Movies] FOREIGN KEY([MovieId])
REFERENCES [dbo].[Movies] ([Id])
GO

ALTER TABLE [dbo].[Editions] CHECK CONSTRAINT [FK_Editions_Movies]
GO

ALTER TABLE [dbo].[Editions]  WITH CHECK ADD  CONSTRAINT [FK_Editions_TemporaryDiscounts] FOREIGN KEY([DiscountId])
REFERENCES [dbo].[TemporaryDiscounts] ([Id])
ON DELETE SET NULL
GO

ALTER TABLE [dbo].[Editions] CHECK CONSTRAINT [FK_Editions_TemporaryDiscounts]
GO


USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[LoanCarts]    Script Date: 04/15/2012 01:03:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LoanCarts](
	[Id] [uniqueidentifier] NOT NULL,
	[LoanDate] [datetime] NOT NULL,
	[CustomerId] [uniqueidentifier] NULL,
	[CartDiscountValue] [float] NOT NULL,
 CONSTRAINT [PK_LoanCarts] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[LoanCarts]  WITH NOCHECK ADD  CONSTRAINT [FK_LoanCarts_Customers] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customers] ([Id])
ON UPDATE SET NULL
GO

ALTER TABLE [dbo].[LoanCarts] NOCHECK CONSTRAINT [FK_LoanCarts_Customers]
GO

ALTER TABLE [dbo].[LoanCarts] ADD  CONSTRAINT [DF_LoanCarts_CartDiscountValue]  DEFAULT ((0)) FOR [CartDiscountValue]
GO

USE [MovieRentalDB]
GO

/****** Object:  Table [dbo].[Loans]    Script Date: 04/15/2012 01:03:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Loans](
	[LoanCartId] [uniqueidentifier] NOT NULL,
	[EditionId] [uniqueidentifier] NULL,
	[PricePerDay] [float] NOT NULL,
	[ReturnDate] [datetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Loans]  WITH CHECK ADD  CONSTRAINT [FK_Loans_Editions] FOREIGN KEY([EditionId])
REFERENCES [dbo].[Editions] ([Id])
ON DELETE SET NULL
GO

ALTER TABLE [dbo].[Loans] CHECK CONSTRAINT [FK_Loans_Editions]
GO

ALTER TABLE [dbo].[Loans]  WITH CHECK ADD  CONSTRAINT [FK_Loans_LoanCarts] FOREIGN KEY([LoanCartId])
REFERENCES [dbo].[LoanCarts] ([Id])
GO

ALTER TABLE [dbo].[Loans] CHECK CONSTRAINT [FK_Loans_LoanCarts]
GO











